package org.lq.er.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.lq.er.dao.Score_infoDao;
import org.lq.er.entity.Score_info;
import org.lq.er.util.Instantiation;
import org.lq.er.util.JDBCUtil;

/**
 * 成绩数据访问接口实现
 * @author 宋铀
 * 
 * @package org.lq.er.dao.impl
 * @date 2020年8月24日
 */
public class Score_infoDaoImpl implements Score_infoDao,Instantiation<Score_info>{

	/**
	 * 添加成绩信息
	 */
	@Override
	public int save(Score_info t) {
		// 插入	sql语句
		String sql = "insert into score_info(id,stu_id,subject_id,score,status) values (?,?,?,?,?)";
		// SQL语句执行结果
		int flag = 0;
		try {
			flag = JDBCUtil.executeUpdate(sql, t.getId(),t.getStu_id(),t.getSubject_id(),t.getScore(),t.getStatus());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 修改成绩信息
	 */
	@Override
	public int update(Score_info t) {
		// 修改	sql语句
		String sql = "update score_info set stu_id=?,subject_id=?,score=?,status=? where stu_id = ? and subject_id = ?";
		// sql语句执行结果
		int flag = 0;
		try {
			flag = JDBCUtil.executeUpdate(sql, t.getStu_id(),t.getSubject_id(),t.getScore(),t.getStatus(),t.getStu_id(),t.getSubject_id());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 删除成绩信息
	 */
	@Override
	public int delete(int id) {
		// 删除	sql语句
		String sql = "delete from score_info where id = ?";
		// sql语句执行结果
		int flag = 0;
		try {
			flag = JDBCUtil.executeUpdate(sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public int delete(String stuId, int subId) {
		String sql = "delete from score_info where stu_id = ? and subject_id=?";
		// sql语句执行结果
		int flag = 0;
		try {
			flag = JDBCUtil.executeUpdate(sql, stuId, subId);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 查询所有
	 */
	@Override
	public List<Score_info> findAll() {
		// 查询结果
		List<Score_info> list = new ArrayList<Score_info>(); 
		// 删除	sql语句
		String sql = "select * from score_info";
		try {
			list = JDBCUtil.executeQuery(sql, this);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 根据Id查询成绩
	 */
	@Override
	public Score_info getById(int id) {
		// 查询结果
		List<Score_info> list = new ArrayList<Score_info>();
		// 创建学生实体类
		Score_info score_info = null;
		// 查询	SQL语句
		String sql = "select * from score_info where id=?";
		try {
			list = JDBCUtil.executeQuery(sql, this,id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (!list.isEmpty()) {			
			score_info = list.get(0);
		} else {
			score_info = null;
		}
		return score_info;
	}

	@Override
	public Score_info instance(ResultSet rs) {
		// 创建一个成绩信息实体类
		Score_info score_info = new Score_info();
		try {
			score_info.setId(rs.getInt("id"));
			score_info.setStu_id(rs.getString("stu_id"));
			score_info.setSubject_id(rs.getInt("subject_id"));
			score_info.setScore(rs.getInt("score"));
			score_info.setStatus(rs.getInt("status"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return score_info;
	}
	/**
	 * 根据身份证号查询成绩
	 */
	@Override
	public Score_info getByStu_id(String stu_id) {
		// 查询结果
		List<Score_info> list = new ArrayList<Score_info>();
		// 创建学生实体类
		Score_info score_info = null;
		// 查询	SQL语句
		String sql = "select * from score_info where stu_id=?";
		try {
			list = JDBCUtil.executeQuery(sql, this,stu_id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (!list.isEmpty()) {			
			score_info = list.get(0);
		} else {
			score_info = null;
		}
		return score_info;
	}

	

}
